SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sptblPersons_RequestBondsnummer' AND ROUTINE_TYPE = 'PROCEDURE')
	DROP PROCEDURE sptblPersons_RequestBondsnummer
GO

CREATE PROCEDURE sptblPersons_RequestBondsnummer
	-- Add the parameters for the stored procedure here
	@prs_ID int,
	@Message VARCHAR(255) OUTPUT	
AS
BEGIN 
	DECLARE @TranCount int
	SET @TranCount = @@TRANCOUNT 
	IF @TranCount > 0
		SAVE TRAN sptblPersons_RequestBondsnummer
	ELSE
		BEGIN TRAN
		
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    BEGIN TRY
      
		UPDATE tblPersons SET prs_State = 'Aangevraagd bond' WHERE prs_ID = @prs_ID
		SET @Message = 'BondsnummerRequested'
		
		IF @TranCount = 0
			COMMIT TRAN	
		
	END TRY
	BEGIN CATCH
	    DECLARE @ErrorMessage NVARCHAR(4000);
	    DECLARE @ErrorSeverity INT;
	    DECLARE @ErrorState INT;
	    SELECT 
		@ErrorMessage = ERROR_MESSAGE(),
		@ErrorSeverity = ERROR_SEVERITY(),
		@ErrorState = ERROR_STATE();
	    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
	    IF @TranCount > 0
			ROLLBACK TRAN sptblPersons_RequestBondsnummer
		ELSE
			ROLLBACK TRAN
	END CATCH
END
GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sptblPersons_RequestBondsnummerAction' AND ROUTINE_TYPE = 'PROCEDURE')
	DROP PROCEDURE sptblPersons_RequestBondsnummerAction
GO

CREATE PROCEDURE sptblPersons_RequestBondsnummerAction
	-- Add the parameters for the stored procedure here
	@prs_ID int	
AS
BEGIN		
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    BEGIN TRY
      
		IF EXISTS ( SELECT 1 FROM dbo.tblPersons WHERE prs_ID = @prs_ID AND prs_State = 'Aspirant lid' AND prs_FederationNr IS NULL )
		BEGIN			
			SELECT CAST(1 AS BIT)
		END
		ELSE
		BEGIN
			SELECT CAST(0 AS BIT)
		END    
	END TRY
	BEGIN CATCH
	    DECLARE @ErrorMessage NVARCHAR(4000);
	    DECLARE @ErrorSeverity INT;
	    DECLARE @ErrorState INT;
	    SELECT 
		@ErrorMessage = ERROR_MESSAGE(),
		@ErrorSeverity = ERROR_SEVERITY(),
		@ErrorState = ERROR_STATE();
	    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);	    
	END CATCH
END
GO

DELETE FROM [tblUFGMenuActions] WHERE [uma_TableName] = 'tblPersons' AND [uma_SequenceNumber] = 2
GO
INSERT INTO [tblUFGMenuActions]
           ([uma_TableName]
           ,[uma_SequenceNumber]
           ,[uma_StoredProcedure]
           ,[uma_Icon]
           ,[uma_ShowTable]
           ,[uma_ShowConditionProcedure]
           ,[uma_ToggleVisible]
           ,[uma_VisibleInGrid]
           ,[uma_PanelTable]
           ,[uma_PanelNumber]
           ,[uma_HAlign]
           ,[uma_VAlign]
           ,[uma_ValidatesData] )          
     VALUES
           ('tblPersons'
           ,2
           ,'sptblPersons_RequestBondsnummer'
           ,'icon_user.gif'
           ,null
           ,'sptblPersons_RequestBondsnummerAction'
           ,1
           ,0
           ,'tblPersons'
           ,1
           ,'Right'
           ,'Top'
           ,1)
GO

INSERT INTO dbo.tblUFGMenuActionRoles
		(uar_TableName,
		 uar_SequenceNumber,
		 uar_RoleName)
	VALUES
		('tblPersons',
		 2,
		 'Ledenadministratie Club'),
		 ('tblPersons',
		 2,
		 'Voorzitter Club')
GO

INSERT INTO dbo.tblUFGMenuActionTitles
		(uat_TableName,
		 uat_SequenceNumber,
		 uat_Language,
		 uat_Title,
		 uat_Tooltip,
		 uat_ShowConditionDescription,
		 uat_ConfirmationMessage)
	VALUES
		('tblPersons', 2, 'Nederlands', 'Bondsnummer aanvragen', 'Bondsnummer aanvragen voor nieuw lid', null, 'Bondsnummer aanvragen?')
GO

INSERT INTO [IMAGine].[dbo].[tblUFGSearchFilters]
           ([usf_SearchFilterName]
           ,[usf_SearchFilterType]
           ,[usf_SearchFilterCondition]
           ,[usf_RoleName]
           ,[usf_UserName]
           ,[usf_TableName]
           ,[usf_IsWork]
           ,[usf_Message]
           ,[usf_Published]
           ,[usf_Category]
           ,[usf_PublishedBlock])
     VALUES
           ('PersonsFedNrRequests'
           ,'Role'
           ,'prs_state = ''Aangevraagd bond'' AND prs_FederationNr IS NULL'
           ,'Ledenadministratie Bond'
           ,null
           ,'tblPersons'
           ,1
           ,'SF_PersonsFedNrRequests'
           ,0
           ,null
           ,'NONE')
GO

INSERT dbo.tblUFGMessages (umm_MessageName, umm_LanguageName, umm_Message)
VALUES ('SF_PersonsFedNrRequests', 'Nederlands', 'Bondsnummers toekennen aan nieuwe leden.')
GO
